🔬 用户、权限、角色全流程实验

📚 数据库原理及应用 📅 第 8 章配套实验 ⏱️ 预计 50 分钟 💻 MySQL 8.0+
🎯 实验目标
  • 能独立完成 MySQL 用户的创建、查看、改名、改密、删除
  • 能在不同层次（全局、库、表、字段）授予和回收权限
  • 能定义角色、给角色授权、把角色分配并激活给用户
  • 遇到「权限不生效」时，能自己排查原因

环境准备

本实验所有命令都在 MySQL 命令行里执行。开始之前请确认 MySQL 服务已启动，且你能用 root 账号登录。

第 1 步：登录 MySQL

SHELL
# 在系统命令行（cmd / PowerShell / Linux 终端）里执行
# -u root 表示用 root 账号登录，-p 表示提示输入密码
mysql -u root -p
⚠️ Windows 用户
如果提示「mysql 不是内部或外部命令」，说明 MySQL 的 bin 目录没加到 PATH 环境变量里。 可以临时切换到 MySQL 的 bin 目录后再执行，例如： cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

第 2 步：创建实验数据库和测试表

本实验使用一个简化的教学数据库 teaching，包含教师表 t 和成绩表 sc。 把下面整段 SQL 复制到 mysql 客户端里一次性执行。

SQL · 一键建库
-- ========== 1. 创建数据库 ==========
DROP DATABASE IF EXISTS teaching;
CREATE DATABASE teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;

-- ========== 2. 教师表 t ==========
CREATE TABLE t (
    tno   CHAR(5)     PRIMARY KEY     -- 教师编号,
    tn    VARCHAR(20) NOT NULL        -- 教师姓名,
    sex   CHAR(2)                          -- 性别,
    dept  VARCHAR(30)                      -- 所在院系,
    title VARCHAR(20)                      -- 职称
);

-- 插入测试数据
INSERT INTO t VALUES
('T001', '张文博', '男', '计算机学院', '教授'),
('T002', '李清扬', '女', '计算机学院', '副教授'),
('T003', '王明远', '男', '数学学院',   '讲师'),
('T004', '赵小晴', '女', '外语学院',   '副教授'),
('T005', '陈大山', '男', '物理学院',   '教授');

-- ========== 3. 成绩表 sc ==========
CREATE TABLE sc (
    sno   CHAR(8)                              -- 学号,
    cno   CHAR(4)                              -- 课程号,
    score DECIMAL(5,1)                          -- 成绩,
    PRIMARY KEY (sno, cno)
);

INSERT INTO sc VALUES
('20240001', 'C001', 87.5),
('20240001', 'C002', 76.0),
('20240002', 'C001', 92.0),
('20240003', 'C001', 59.0),
('20240003', 'C002', 68.5);

-- 验证一下
SELECT COUNT(*) AS 教师数 FROM t;
SELECT COUNT(*) AS 成绩数 FROM sc;
💡 应该看到的结果
教师数 = 5，成绩数 = 5。如果不对说明上面的 SQL 没全部执行成功，重新跑一遍。

第 3 步：清理潜在冲突（如果之前做过实验）

实验中会反复创建一些固定名字的用户和角色。如果你之前做过类似实验，先把同名的清理掉，避免冲突。

SQL · 选做清理
-- 用 IF EXISTS，不存在时不报错
DROP USER IF EXISTS 'student1'@'localhost';
DROP USER IF EXISTS 'teacher_a'@'%';
DROP USER IF EXISTS 'teacher_b'@'localhost';
DROP USER IF EXISTS 'guest'@'localhost';
DROP ROLE IF EXISTS 'role_teacher';
DROP ROLE IF EXISTS 'role_reader';

⭐ 基础任务（用户管理）

本组任务对应教材 8.3 节。每题都会给出参考语法提示，第一次操作不要怕——错了就改，反正用户都是新建的。

任务 1 ⭐ 基础 · 创建用户

创建一个用户名为 student1、密码为 stu123 的用户， 只允许从本机登录。创建完毕后查询 mysql.user 表确认用户存在。

💬 参考语法
CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
应在 mysql.user 表中查到一条 User='student1'、Host='localhost' 的记录。
🔑 查看参考答案
SQL
-- 创建用户
CREATE USER 'student1'@'localhost' IDENTIFIED BY 'stu123';

-- 验证
SELECT User, Host FROM mysql.user
WHERE User = 'student1';

关键点：'localhost' 限定来源主机，所以只能从本机连。 如果想让它从任意地方登录，主机改 '%'。

任务 2 ⭐ 基础 · 列出所有用户

查询 mysql.user 表，列出当前 MySQL 服务器上所有用户的用户名和主机， 按用户名升序排列。

应至少看到 root、mysql.sys、mysql.session 等系统用户，以及刚刚创建的 student1。
🔑 查看参考答案
SQL
SELECT User, Host
FROM mysql.user
ORDER BY User;

除了普通用户，你会看到几个 mysql.* 开头的系统内置账号，它们是 MySQL 自己用的，不要去删。

任务 3 ⭐ 基础 · 修改密码

把 student1 的密码改成 newpwd2025，使用 ALTER USER 语句完成。 改完后退出 MySQL，用新密码重新登录验证。

💬 参考语法
ALTER USER '用户名'@'主机' IDENTIFIED BY '新密码';
用旧密码登录会失败，用新密码登录成功。
🔑 查看参考答案
SQL
-- 在 root 会话里执行
ALTER USER 'student1'@'localhost' IDENTIFIED BY 'newpwd2025';
SHELL · 验证登录
# 退出当前 mysql 会话
exit

# 用 student1 + 新密码登录
mysql -u student1 -p
# 然后输入 newpwd2025

登录成功就证明密码改对了。验证完别忘了 exit 退回 root 会话继续做后面的题。

任务 4 ⭐ 基础 · 重命名用户

把 student1@localhost 重命名为 guest@localhost。 然后查询确认旧名已不存在、新名已生效。

user 表中查不到 student1，但能查到 guest。
🔑 查看参考答案
SQL
RENAME USER 'student1'@'localhost' TO 'guest'@'localhost';

-- 验证
SELECT User, Host FROM mysql.user
WHERE User IN ('student1', 'guest');

结果中应该只看到 guest，看不到 student1。重命名后密码不变，还是 newpwd2025。

任务 5 ⭐ 基础 · 删除用户

删除刚才重命名得到的 guest@localhost 用户。 用 DROP USER IF EXISTS 形式，避免再次执行时报错。

user 表中查不到 guest 用户。
🔑 查看参考答案
SQL
DROP USER IF EXISTS 'guest'@'localhost';

-- 验证
SELECT User FROM mysql.user
WHERE User = 'guest';
-- 应该 0 行

DROP USER 会一并删除该用户的所有权限记录，干净彻底。

⭐⭐ 提高任务（权限管理）

本组任务对应教材 8.4 节。重点体会「不同层级权限」的差别，以及「授权 → 用 → 收权 → 用」整个流程。

任务 6 ⭐⭐ 提高 · 数据库级授权

创建一个新用户 teacher_a，密码 ta123，主机为 %。 然后授予它 teaching 数据库下所有表的 SELECT 权限。 最后用 SHOW GRANTS 查看授权结果。

SHOW GRANTS 的结果应该包含一行类似 GRANT SELECT ON `teaching`.* TO `teacher_a`@`%`。
🔑 查看参考答案
SQL
-- 1. 先创建用户（MySQL 8 不允许给不存在的用户授权）
CREATE USER 'teacher_a'@'%' IDENTIFIED BY 'ta123';

-- 2. 授予 teaching 库下所有表的 SELECT 权限
GRANT SELECT
    ON teaching.*
    TO 'teacher_a'@'%';

-- 3. 查看授权情况
SHOW GRANTS FOR 'teacher_a'@'%';

预期结果两行：一行是 GRANT USAGE ON *.*（占位行），一行是 GRANT SELECT ON `teaching`.*。USAGE 不是你授的，是 MySQL 自己加的。

任务 7 ⭐⭐ 提高 · 字段级授权

再授予 teacher_a 在 teaching.t 表上的字段级更新权限—— 只允许它修改 title（职称）字段，不允许改其他字段。 用 SHOW GRANTS 验证。

💬 参考语法
字段级权限的字段名写在权限关键字后面的括号里：
GRANT UPDATE(字段名) ON 库.表 TO '用户'@'主机';
SHOW GRANTS 中会多出一行类似 GRANT UPDATE (title) ON `teaching`.`t` TO ...。
🔑 查看参考答案
SQL
-- 字段级 UPDATE：只能改 title 列
GRANT UPDATE(title)
    ON teaching.t
    TO 'teacher_a'@'%';

SHOW GRANTS FOR 'teacher_a'@'%';

真要测试字段级限制是否生效，可以另开一个终端用 teacher_a 登录，分别尝试 UPDATE title 和 UPDATE tn， 你会看到改 title 成功、改 tn 报错（"command denied to user"）。

任务 8 ⭐⭐ 提高 · 权限回收

回收 teacher_a 在 teaching 库上的 SELECT 权限， 保留它对 t 表 title 字段的 UPDATE 权限。回收后再次 SHOW GRANTS。

SHOW GRANTS 中不再有 GRANT SELECT ON `teaching`.* 这一行， 但 GRANT UPDATE (title) ON `teaching`.`t` 仍然存在。
🔑 查看参考答案
SQL
REVOKE SELECT
    ON teaching.*
    FROM 'teacher_a'@'%';

SHOW GRANTS FOR 'teacher_a'@'%';

核心点：REVOKE 只回收明确指定的那部分权限，其他层级、其他对象的权限不受影响。 要把所有权限一起收，可以用 REVOKE ALL PRIVILEGES, GRANT OPTION FROM ...。

任务 9 ⭐⭐ 提高 · 权限转移

创建用户 teacher_b@localhost，密码 tb123。 给它 teaching.sc 表的 SELECT 和 INSERT 权限，并允许它把这两个权限再授给别人。 用 SHOW GRANTS 验证 WITH GRANT OPTION 已生效。

SHOW GRANTS 的结果中，授权语句末尾应该有 WITH GRANT OPTION。
🔑 查看参考答案
SQL
-- 1. 创建用户
CREATE USER 'teacher_b'@'localhost' IDENTIFIED BY 'tb123';

-- 2. 带 WITH GRANT OPTION 的授权
GRANT SELECT, INSERT
    ON teaching.sc
    TO 'teacher_b'@'localhost'
    WITH GRANT OPTION;

-- 3. 验证
SHOW GRANTS FOR 'teacher_b'@'localhost';

看到末尾的 WITH GRANT OPTION 就说明转授权能力已经给上了。 接下来 teacher_b 自己登录后，就能用 GRANT 把它有的这两个权限再授给其他用户。

⭐⭐⭐ 拓展任务（角色管理）

本组任务对应教材 8.5 节。这是本章最容易出错的地方——尤其是「分配角色后忘了激活」这个坑， 强烈建议踩一遍，记一辈子。

任务 10 ⭐⭐⭐ 拓展 · 创建角色 + 给角色授权

创建一个名为 role_teacher 的角色，授予它 teaching.t 和 teaching.sc 两张表的全部增删改查权限（SELECT、INSERT、UPDATE、DELETE）。

用 SHOW GRANTS FOR 'role_teacher'; 应该能看到对两张表分别授权的两行 GRANT 语句。
🔑 查看参考答案
SQL
-- 1. 创建角色（角色名也是字符串，要单引号）
CREATE ROLE 'role_teacher';

-- 2. 给角色授权（语法和给用户授权完全一样）
GRANT SELECT, INSERT, UPDATE, DELETE
    ON teaching.t
    TO 'role_teacher';

GRANT SELECT, INSERT, UPDATE, DELETE
    ON teaching.sc
    TO 'role_teacher';

-- 3. 验证角色权限
SHOW GRANTS FOR 'role_teacher';

从语法上看，角色就像一个「不能登录的用户」——能拥有权限，但不能用账号密码登进 MySQL。

任务 11 ⭐⭐⭐ 拓展 · 把角色分配给用户 + 激活

把刚才创建的 role_teacher 角色分配给 teacher_a 用户，并设置为登录时默认激活。 然后用 teacher_a 重新登录 MySQL，执行 SELECT CURRENT_ROLE(); 确认角色已生效。

⚠️ 这一步特别容易出错
很多同学只做 GRANT 不做 SET DEFAULT ROLE，结果 teacher_a 登进去后查不了表，怎么也想不通。 必须两步都做。
teacher_a 登录后执行 SELECT CURRENT_ROLE();，结果应该返回 `role_teacher`@`%` 而不是 NONE。
🔑 查看参考答案
SQL · 在 root 会话里
-- 1. 把角色分配给用户（建立绑定关系）
GRANT 'role_teacher' TO 'teacher_a'@'%';

-- 2. 关键步骤：设置默认激活
--    没这一步，下一次登录角色不会自动生效
SET DEFAULT ROLE 'role_teacher' TO 'teacher_a'@'%';
SHELL · 切到 teacher_a 验证
# 退出 root 会话
exit

# 用 teacher_a 登录（密码 ta123）
mysql -u teacher_a -p
SQL · teacher_a 会话里执行
-- 查看当前生效的角色
SELECT CURRENT_ROLE();
-- 应返回 `role_teacher`@`%`，而不是 NONE

-- 试一下角色赋予的权限
USE teaching;
SELECT * FROM t LIMIT 3;
-- 能查出数据，说明角色生效了

如果忘了 SET DEFAULT ROLE，CURRENT_ROLE() 会返回 NONE， teacher_a 登录后还是只能行使它自己的权限（之前授的 UPDATE title），看不到角色带来的新权限。

任务 12 ⭐⭐⭐ 拓展 · 角色撤销 + 删除

回到 root 会话，把 role_teacher 角色从 teacher_a 用户身上回收， 然后把整个 role_teacher 角色彻底删除。让 teacher_a 再次登录， 检查 SHOW GRANTS 中是否还有角色相关条目。

❌ 别把语法写反了
回收角色的语法是「REVOKE 角色 FROM 用户」，被回收的是角色。 不要写成 REVOKE 'teacher_a' FROM 'role_teacher';——那个是错的。
teacher_a 的 SHOW GRANTS 中不再有 role_teacher 相关条目；mysql 里执行 SELECT * FROM mysql.user WHERE User='role_teacher'; 应返回空。
🔑 查看参考答案
SQL · root 会话
-- 1. 把角色从用户身上拿走
--    顺序：REVOKE 角色 FROM 用户
REVOKE 'role_teacher' FROM 'teacher_a'@'%';

-- 2. 删除角色（DROP ROLE 自动解除该角色对所有用户的绑定）
DROP ROLE 'role_teacher';

-- 3. 验证角色已被删除
SELECT User, Host FROM mysql.user
WHERE User = 'role_teacher';
-- 应返回 0 行

实际上，第 2 步 DROP ROLE 自带「从所有绑定它的用户身上拿走」的副作用， 所以即使省略第 1 步直接 DROP ROLE 也能达到同样效果。但分两步写更安全清晰， 让你明确知道「先解绑、再删除」这个动作流。

挑战题 ⭐⭐⭐ 挑战 · 综合排错

设想一个场景：你给用户 jerry@localhost 创建了一个角色 role_reader， 并授予 teaching.* 的 SELECT 权限，把角色 GRANT 给 jerry。 可是 jerry 登录后执行 SELECT CURRENT_ROLE(); 返回 NONE， SELECT 表也报权限不足。请你写出完整的排查思路和修复 SQL。

🔑 参考思路

排查思路：

  1. 先确认角色本身是否有权限：SHOW GRANTS FOR 'role_reader';
  2. 再确认角色是否分配给了 jerry：SHOW GRANTS FOR 'jerry'@'localhost'; 看是否有 GRANT `role_reader` TO ... 这一行
  3. 最关键：检查角色是否被设为默认激活——这是新手最常犯的错

修复 SQL：

SQL
-- 在 root 会话里补上激活
SET DEFAULT ROLE 'role_reader' TO 'jerry'@'localhost';

-- 或者一步到位激活该用户拥有的所有角色
SET DEFAULT ROLE ALL TO 'jerry'@'localhost';

-- 然后让 jerry 重新登录，验证
-- SELECT CURRENT_ROLE();  应返回 `role_reader`@`%`

临时方案：jerry 也可以在自己当前会话里用 SET ROLE 'role_reader'; 临时激活，但下次登录还是会失效——治标不治本，根本解决靠管理员设默认激活。

实验完成检查

✅ 自我评估

勾选你已完成的项目，进度条会自动更新。完成所有基础和提高任务，本次实验就达标了。

完成进度：0 / 6

🧹 清理实验环境（选做）

如果不想留下实验产生的用户和角色，可以一键清理：

SQL · 清理
-- 删除实验中创建的所有用户和角色
DROP USER IF EXISTS 'student1'@'localhost';
DROP USER IF EXISTS 'guest'@'localhost';
DROP USER IF EXISTS 'teacher_a'@'%';
DROP USER IF EXISTS 'teacher_b'@'localhost';
DROP ROLE  IF EXISTS 'role_teacher';
DROP ROLE  IF EXISTS 'role_reader';

-- teaching 库要不要删，看你下次还做不做后续实验
-- 不删：DROP DATABASE IF EXISTS teaching;